package com.export.xlsx.dao.impl;

import com.export.xlsx.dao.CompanyDao;
import com.export.xlsx.domain.Company;
import com.export.xlsx.utils.DateUtils;
import com.export.xlsx.utils.JdbcUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

/**
 * Created by zhangls on 2018/1/8 0008.
 */
public class CompanyDaoImpl implements CompanyDao {

    @Override
    public Boolean addCompany(List<Company> ls) {
        try {
            String sql = "INSERT INTO tb_temp1(ID,COMPANY_CODE,COMPANY_NAME,PARK_CODE,PARK_NAME,CREDIT_CODE,COMPANY_ADDR,TAX_ADDR) VALUES(?, ?, ?, ?, ?, ?, ?, ?)";
            QueryRunner query = new QueryRunner(JdbcUtils.getDataSource());
            Object[][] params = new Object[ls.size()][];

            for (int i = 0; i < ls.size(); i++) {
                Company company = ls.get(i);
                params[i] = new Object[8];
                params[i][0] = company.getC0();
                params[i][1] = company.getC1();
                params[i][2] = company.getC2();
                params[i][3] = company.getC3();
                params[i][4] = company.getC4();
                params[i][5] = company.getC5();
            }

            int[] isi = query.batch(sql, params);

            return Boolean.TRUE;
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException();
        }
    }

    @Override
    public Boolean addCompany(Company company){
        try {
            QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
            String sql = "INSERT INTO TB_COMPANY(COMPANY_CODE, COMPANY_NAME, PARK_ID, CREDIT_CODE,COMPANY_ADDR, TAX_ADDR) VALUES(?, ?, ?, ?, ?, ?)";
            Object params[] = {
                    company.getC0(),//机构代码
                    company.getC1(),//机构名称
                    company.getC2(),//分园代码
                    company.getC3(),//信用代码
                    company.getC4(),//经营地址
                    company.getC5() //注册地址
            };
            System.out.println("company = " + company);
            return runner.update(sql, params) > 0;
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException();
        }
    }

    @Override
    public Company queryByName(Company company) {
        try {
            QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
            String sql = "SELECT COMPANY_CODE, COMPANY_NAME, PARK_ID, CREDIT_CODE,COMPANY_ADDR, TAX_ADDR FROM TB_COMPANY t WHERE t.COMPANY_NAME=? AND t.PARK_ID=?";
            Object[] params = {company.getC1(),company.getC2()};
            ResultSetHandler<Company> rsh = new BeanHandler<Company>(Company.class) {

                @Override
                public Company handle(ResultSet rs) throws SQLException {
                    Company c = null;
                    while (rs.next()){
                        c = new Company();
                        c.setC0(rs.getString("COMPANY_CODE"));
                        c.setC1(rs.getString("COMPANY_NAME"));
                        c.setC2(rs.getString("PARK_ID"));
                        c.setC3(rs.getString("CREDIT_CODE"));
                        c.setC4(rs.getString("COMPANY_ADDR"));
                        c.setC5(rs.getString("TAX_ADDR"));
                    }
                    return c;
                }
            };
            return runner.query(sql, rsh, params);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException();
        }
    }

    @Override
    public Boolean updC3(Company company) {
        try {
            String sql = "UPDATE TB_COMPANY SET CREDIT_CODE=? WHERE COMPANY_NAME=? AND PARK_ID=?";
            QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
            Object[] params = {company.getC3(),company.getC1(), company.getC2()};

            return runner.update(sql,params) > 0;
        }catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException();
        }
    }

    @Override
    public Boolean updC4(Company company) {
        try {
            String sql = "UPDATE TB_COMPANY SET COMPANY_ADDR=? WHERE COMPANY_NAME=? AND PARK_ID=?";
            QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
            Object[] params = {company.getC4(),company.getC1(), company.getC2()};

            return runner.update(sql,params) > 0;
        }catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException();
        }
    }

    @Override
    public Boolean updC5(Company company) {
        try {
            String sql = "UPDATE TB_COMPANY SET TAX_ADDR=? WHERE COMPANY_NAME=? AND PARK_ID=?";
            QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
            Object[] params = {company.getC5(),company.getC1(), company.getC2()};

            return runner.update(sql,params) > 0;
        }catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException();
        }
    }

    @Override
    public Boolean updateInfo(Company company, String phone, String person, String email, String addr, String taxAddr, String empSum, String techSum, String creditCode, String taxpayerCode, String regMon, String runPro, Integer comType, String establishmentDate, String recordDate, String regOrg) {
        try {
            String sql = "UPDATE TB_COMPANY SET COMPANY_ADDR=?, TAX_ADDR=?, LEGAL_PERSON=?, TEL_NUM=?, EMAIL=?, EMP_SUM=?, TECH_EMP=?, CREDIT_CODE=?, taxpayer_code=?, reg_mon=?, run_pro=?, company_type=?, establishment_date=?, record_date=?, reg_org=? WHERE COMPANY_NAME=? AND PARK_ID=?";
            QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
            Object[] params = {
                    addr,
                    taxAddr,
                    person,
                    phone,
                    email,
                    "".equals(empSum) ? null : empSum,
                    "".equals(techSum) ? null : techSum,
                    "".equals(creditCode) ? null : creditCode,
                    "".equals(taxpayerCode) ? null : taxpayerCode,
                    "".equals(regMon) ? null : regMon,
                    "".equals(runPro) ? null : runPro,
                    comType,
                    "".equals(establishmentDate) ? null : establishmentDate,
                    "".equals(recordDate) ? null : recordDate,
                    "".equals(regOrg) ? null : regOrg,
                    company.getC1(),
                    company.getC2()
            };

            return runner.update(sql,params) > 0;
        }catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException();
        }
    }

    @Override
    public Boolean insertInfo1(Company company, String value, Integer typeNum, Integer year) {
        try {
            String sql = "INSERT INTO TB_COMPANY_FEE(COMPANY_NAME, PARK_ID, FEE_ID, YEAR_ID, FEE_VALUE) VALUES(?,?,?,?,?)";
            QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
            Object[] params = {company.getC1(),company.getC2(), typeNum, year, value};

            return runner.update(sql,params) > 0;
        }catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException();
        }
    }

    @Override
    public Boolean insertInfo(Company company, String phone, String person, String email, String addr, String taxAddr, String empSum, String techSum, String creditCode, String taxpayerCode, String regMon, String runPro, Integer comType, String establishmentDate, String recordDate, String regOrg) {
        try {
            String sql = "INSERT INTO TB_COMPANY(COMPANY_NAME,PARK_ID,COMPANY_ADDR,TAX_ADDR,LEGAL_PERSON,TEL_NUM,EMAIL,EMP_SUM,TECH_EMP,CREDIT_CODE,taxpayer_code,reg_mon,run_pro,company_type,establishment_date,record_date,reg_org) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
            Object params[] = {
                    company.getC1(),
                    company.getC2(),
                    addr,
                    taxAddr,
                    person,
                    phone,
                    email,
                    "".equals(empSum) ? null : empSum,
                    "".equals(techSum) ? null : techSum,
                    "".equals(creditCode) ? null : creditCode,
                    "".equals(taxpayerCode) ? null : taxpayerCode,
                    "".equals(regMon) ? null : regMon,
                    "".equals(runPro) ? null : runPro,
                    comType,
                    "".equals(establishmentDate) ? null : establishmentDate,
                    "".equals(recordDate) ? null : recordDate,
                    "".equals(regOrg) ? null : regOrg
            };

            return runner.update(sql, params) > 0;
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException();
        }
    }

    @Override
    public Boolean updateInfo1(Company company, String phone, String person, String email, String addr, String empSum, String techSum, String manageSum) {
        try {
            String sql = "UPDATE TB_COMPANY SET COMPANY_ADDR=?, LEGAL_PERSON=?, TEL_NUM=?, EMAIL=?, EMP_SUM=?, TECH_EMP=?, manage_emp=? WHERE COMPANY_NAME=? AND PARK_ID=?";
            QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
            Object[] params = {addr,person, phone,email,empSum,techSum,manageSum,company.getC1(),company.getC2()};

            return runner.update(sql,params) > 0;
        }catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException();
        }
    }

    @Override
    public Boolean insertInfo2(Company company, String phone, String person, String email, String addr, String empSum, String techSum, String manageSum) {
        try {
            String sql = "INSERT INTO TB_COMPANY(COMPANY_NAME,PARK_ID,COMPANY_ADDR,LEGAL_PERSON,TEL_NUM,EMAIL,EMP_SUM,TECH_EMP,manage_emp) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)";
            QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
            Object params[] = {
                    company.getC1(),
                    company.getC2(),
                    addr,
                    person,
                    phone,
                    email,
                    "".equals(empSum) ? null:empSum,
                    "".equals(techSum) ? null:techSum,
                    "".equals(manageSum) ? null:manageSum
            };

            return runner.update(sql, params) > 0;
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException();
        }
    }

    @Override
    public Boolean updateBenke(Company company, String value, Integer typeNum, Integer year) {
        try {
            String sql = "UPDATE TB_COMPANY_FEE SET FEE_VALUE=? WHERE FEE_ID=? AND COMPANY_NAME=? AND PARK_ID=? AND YEAR_ID=?";
            QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
            Object[] params = {value, typeNum, company.getC1(), company.getC2(), year};

            return runner.update(sql,params) > 0;
        }catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException();
        }
    }
}
